package JurisPro.modelo.banco;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import JurisPro.modelo.Usuario;

public class UsuariosDAO {
		
	private static UsuariosDAO singleton = null;
	
	public static UsuariosDAO getInstance() {
		if(singleton == null)
			singleton = new UsuariosDAO();
		return UsuariosDAO.singleton;
	}

	
	public int insertUser(Usuario user) {
		String sql = "INSERT INTO Usuario " +
			"(login, senha, nome, telefone, tipo, bloqueado)"+
		" VALUES (?, ?, ?, ?, ?, 'False' )";
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		int rows = 0;
		try {
			state.setString(1, user.getLogin());
			state.setString(2, user.getSenha());
			state.setString(3, user.getNome());
			state.setString(4, user.getTelefone());
			state.setInt(5, user.getTipo());
			rows = state.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}										
		return rows;		
	}
	
	public int updateUser(Usuario user) {
		
		String sql = "UPDATE Usuario SET login = ?, senha = ? , nome = ?, telefone = ? , tipo = ?"+
		", bloqueado = ? WHERE IDUsuario = ?;";
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		int rows = 0;
		try {
			state.setString(1, user.getLogin());
			state.setString(2, user.getSenha());
			state.setString(3, user.getNome());
			state.setString(4, user.getTelefone());
			state.setInt(5, user.getTipo());
			state.setBoolean(6, user.isBloqueado());
			state.setInt(7, user.getId());
			rows = state.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}										
		return rows;														
	}
	
	public int deleteUser(int id) {
			String sql = "DELETE FROM Usuario WHERE ID = ?;";
			PreparedStatement state = Conexao.getInstance().getPrepState(sql);
			int rows = 0;
			try {
				state.setInt(1, id);
				rows = state.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}										
			return rows;
			
			
			//return Conexao.getInstance().updateComand(sql);			
	}
	
	
	public Usuario selectUserByID(int id) {
		String sql = "SELECT IDUsuario, login, senha, nome, telefone, tipo, bloqueado FROM Usuario where IDUsuario = "+id+" ORDER BY nome;";
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		Usuario user = null;
		try {
			while (rs.next()) {
				user = new Usuario(rs.getInt("IDUsuario"), rs.getString("login"), rs.getString("senha"), 
						rs.getString("nome"), rs.getString("telefone"), rs.getInt("tipo"));
				user.bloquearUsuario(rs.getBoolean("bloqueado"));
			}
			rs.close();	
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return user;
	}
	
	public ArrayList<Usuario> selectUsersAll() {
		String sql = "SELECT IDUsuario, login, senha, nome, telefone, tipo, bloqueado FROM Usuario ORDER BY nome;";
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		Usuario user = null;
		ArrayList<Usuario> users = new ArrayList<Usuario>();
		try {
			while (rs.next()) {
				user = new Usuario(rs.getInt("IDUsuario"), rs.getString("login"), rs.getString("senha"), 
						rs.getString("nome"), rs.getString("telefone"), rs.getInt("tipo"));
				user.bloquearUsuario(rs.getBoolean("bloqueado"));
				users.add(user);
			}
			rs.close();	
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return users;
	}
	public ArrayList<Usuario> selectUsersByName(String nome) {
		String sql = "SELECT IDUsuario, login, senha, nome, telefone, tipo, bloqueado FROM Usuario WHERE nome LIKE "+nome+" ORDER BY nome;";
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		Usuario user = null;
		ArrayList<Usuario> users = new ArrayList<Usuario>();
		try {
			while (rs.next()){
				user = new Usuario(rs.getInt("IDUsuario"), rs.getString("login"), rs.getString("senha"), 
						rs.getString("nome"), rs.getString("telefone"), rs.getInt("tipo"));
				user.bloquearUsuario(rs.getBoolean("bloqueado"));
				users.add(user);
			}
			rs.close();	
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return users;
			
		
	}
	
	public boolean userAuthentication(Usuario user){
		String sql = "SELECT login, senha, bloqueado FROM Usuario where login = ? and senha = ? ORDER BY nome;";
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		try {			
			state.setString(1, user.getLogin());
			state.setString(2, user.getSenha());		
			ResultSet rs = state.executeQuery();
			while (rs.next()){
				if(!rs.getBoolean("bloqueado"))	return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public Usuario userAuthenticated(Usuario user){
		String sql = "SELECT IDUsuario, login, senha, nome, telefone, tipo, bloqueado FROM Usuario where login = ? and senha = ? ORDER BY nome;";
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		try {			
			state.setString(1, user.getLogin());
			state.setString(2, user.getSenha());		
			ResultSet rs = state.executeQuery();
			while (rs.next()){
				user.setId(rs.getInt("IDUsuario"));
				user.setNome(rs.getString("nome"));
				user.setTelefone(rs.getString("telefone"));
				user.setTipo(rs.getInt("tipo"));
				user.bloquearUsuario(rs.getBoolean("bloqueado"));
				return user;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	

}

